﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;

using System.Text;
using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.ESS.DAL
{
    public class D_Message
    {
        ConvertNullable.ConvertNullable cn = new ConvertNullable.ConvertNullable();

        public D_Message()
        {

        }

        public DataSet LoadMessage(Nullable<int> EmpSequence, string MessageStatus)
        {
            string strSQL;
            DataRowCollection drc = null;
            DataSet ds = null;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(2, "@MessageStatus", MessageStatus);

                strSQL = "SELECT t.* FROM("
                    + " select s.to_emp_seq"
                        + ", concat(np2.prefix_name, e2.first_name, '  ', e2.last_name) as to_emp_name"
                    + " from message_send s"
                    + " left join hrm_ps_employees e1 on s.from_emp_seq = e1.emp_seq"
                    + " left join hrm_ps_employees e2 on s.to_emp_seq = e2.emp_seq"
                    + " left join mas_name_prefixes np2 on e2.name_prefix_seq = np2.name_prefix_seq"
                    + " where s.record_status <> @DeleteRecord"

                    + " union all"

                    + " select r.to_emp_seq"
                        + ", concat(np21.prefix_name, e21.first_name, '  ', e21.last_name) as to_emp_name"
                    + " from message_reply r"
                    + " left join hrm_ps_employees e11 on r.from_emp_seq = e11.emp_seq"
                    + " left join hrm_ps_employees e21 on r.to_emp_seq = e21.emp_seq"
                    + " left join mas_name_prefixes np21 on e21.name_prefix_seq = np21.name_prefix_seq"
                    + " where r.record_status <> @DeleteRecord"

                    + " )t";
                    //+ (to_emp_seq != null ? " AND send.to_emp_seq = @to_emp_seq" : "")
                    //+ (from_emp_seq != null ? " AND send.from_emp_seq = @from_emp_seq" : "")
                    //+ (message_status != null ? " AND send.message_status = @message_status" : "")
                    //+ " ORDER BY send.send_date";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return ds;
        }

    }
}
